Introduction to pandas 🐼

(Víctor Sojo | vsojo@amnh.org)

In this notebook we will learn how to use pandas, a mighty data-analysis tool for Python. The main type of object in pandas is the DataFrame, which is a type of dynamic table (similar to an Excel spreadsheet) in which you can access, filter, sort and manipulate columns and rows in extremely efficient and powerful ways. If you have ever worked in R, pandas' DataFrames work very similarly to the ones in R (in fact, in the following notebook we will learn that we can easily interface R and Python within Jupyter).

References:

 Getting started
 Creating a pandas.DataFrame())
 Exploring the size(s) of a dataframe-of-a-dataframe)
 Accessing columns in a dataframe
 Arithmetic operations across entire columns
 Adding columns to a dataframe
 Deleting or \"dropping\" columns
 Renaming columns
 Applying functions on columns
 Getting the data types of columns
 Boolean operations and filtering/subsetting the rows in a dataframe
 Using .loc[] and .iloc[] to filter and explore rows and columns
 Using .loc (or .iloc) to replace values in a dataframe-to-replace-values-in-a-dataframe)
 Writing and reading data to/from text files
 Loading in a more realistic dataset
 Use pivot to transform a stacked dataframe to a multi-indexed one
 Flattening a multi-index dataframe
 Resetting the index of a dataframe
 Getting more than a sample of rows
 Summary statistics
 Merging dataframes
 Comparing two columns
 Replacing specific values in a dataframe with .replace())
 Computing aggregate statistics per group with .groupby())
 Sorting columns
 Plotting pandas data
 Sending pandas dataframes to R and vice versa

Getting started

Creating a conda environment

In case you haven't done so already, let's first create a conda environment specifically for data analysis. Open a new terminal or Anaconda prompt window and do the following:

conda update --all -y
conda create -n data python=3.9 jupyter pandas matplotlib plotly scikit-learn rpy2 r-essentials -y

(You will recognise some of those packages already. I will explain what the others do as we use them.)

Once you've done that, if you've got this notebook open in Jupyter:

  1. Press "Quit" on the main Jupyter browser window (or hit Ctrl+C twice on the terminal tab that's running Jupyter).
  2. Close this notebook.
  3. Activate and start Jupyter again, but from the newly created data environment:
    conda activate data
jupyter notebook

If everything went well, the following should print out "data":

⚠️ If you're on Windows, remember that every line starting with a !, such as ! some code should be changed to !wsl some code, and you need to have an active WSL installation.

Importing required libraries

We will need:

Module Use
pandas Python's dataframe tool.

Note that we're giving pandas an alias of pd. This is completely unnecessary but everybody does it because it makes the code shorter and arguably easier to read.

You will notice below that we're also using matplotlib and plotly, but we didn't import them here (or anywhere else in this notebook). This works because we will be using pandas internal methods to plot directly, so pandas will kindly take care of any imports it needs as they become necessary.

Creating a pandas.DataFrame()

There are many ways to create a pandas.Dataframe from scratch. One of the easiest is with a dictionary, wherein each key is a column name and each value is a list of row values for that column.

In the example below we define two columns:

  1. 'threes' contains a few multiples of 3.
  2. 'squares' contains the squares of those numbers.

As you can see, pandas adds an extra index column at the left, which, in traditional Python fashion, starts counting at 0 and keeps going up one by one (just like the indices in a list or in a numpy.array).

You may remember two things from our previous lessons on Jupyter:

  1. If the last cell is the name of a variable or a simple value, Jupyter prints it out to screen.
  2. I originally recommended that you generally don't make use this behaviour and instead use print() to explicitly print anything to screen. I will backtrack from that recommendation in the case of pandas, because pandas dataframes are printed much more nicely in Jupyter if you just write their name in the last line, without print():

⚠️ There are many more ways to create pandas dataframes!

There must be at least a dozen ways to create a pandas dataframe. Above we used a basic dictionary method in which we have column_name:[values, in, the, column], and below we will read in data from a file. In my view these two are by far the most common and intuitive, but there are many other ways, described in the pandas documentation. Here's a nice unofficial tutorial.

Exploring the size(s) of a dataframe

From the shape, we can trivially extract the number of rows (df.shape[0]) and the number of columns (df.shape[1]); but if you want to be a bit safer, you can try the methods below.

Nnumber of rows

So, the built-in Python function len() returns the number of records or entries in the dataframe, i.e., the number of rows.

Number of columns

And with that, let's look a little closer into how to access columns in a dataframe.

Accessing columns in a dataframe

The two main ways to extract the values of a column are:

Accessing dataframe columns using object.attribute notation

Accessing dataframe columns using dictionary['key'] notation

The advantage of object notation is that it looks really neat and is therefore easy to read and quick to write. Conversely, dictionary notation is clunky to write, but it has the advantages that the key can have spaces in its name, and also you can pass the column name (the key) as a variable:

Note: dictionary notation is more universal and robust, i.e. it works in most cases, whereas attribute notation does not (e.g. for creating or deleting columns, you must use dict notation).

By the way, you may notice that the last line tells us the name of the column (threes) but it also tells us that its dtype is int64, an integer of 64 bits. We study data types in more detail in the numpy lesson; in brief: wherever possible, pandas leverages numpy to optimise the contents of dataframes. This means that, because all elements in the threes column are integers, pandas can store them much more efficiently in memory.

Accessing multiple columns

There are several ways to achieve this. A good one is to use double brackets, with the desired columns separated by commas inside the inner brackets:

In this case, those were our only two columns, so we just get the entire dataframe again, but you can choose whichever columns you wish, in any order. For example, try putting 'squares' first and 'threes' second.

Arithmetic operations across entire columns

We can do basic operations on each of the elements of a column very efficiently:

And we can also do operations side-by-side between columns. For example, we can multiply the column with the multiples of 3 by the column that has their squares, to get the cubes:

Our dataframe remains unaltered though:

So let's see how to keep the result in a new column if we wish.

Adding columns to a dataframe

For this, you just specify the name of the new column and tell pandas what you want it to contain:

⚠️ Note that you cannot use object.attribute notation for the name of the new column, you must use dictionary['key'] notation. However, for the values used in the calculation, you can use object notation if you wish (as we did above for the first calculation) or dict notation (as we did for the second calculation).

Columns don't have to be calculated from other columns, you can just add columns with whatever content you wish, as long as the list you provide is as long as the number of rows in the dataframe:

⚠️ Just note that the length of the new column must be the same as the number of rows in the dataframe.

Deleting or "dropping" columns

This is trivially done by two methods. One is using Python's del keyword:

Just like for adding columns, you must use dictionary notation to remove a column with del, i.e., if you had instead tried:

del df.squares

you would have gotten an error.

The other method to remove a column is to "drop" it by using the pandas method .drop(columns=[col1, col2, ...]):

⚠️ We need to specify inplace=True so that we make the change into the dataframe itself, as opposed to returning a changed version of it (the default behaviour).

Renaming columns

To rename column(s), pandas uses a dictionary syntax, in which the key is given as old_name and the value is the new_name. For example, to rename both the sqroots and names columns:

Just like for dropping a column, we need inplace=True.

⚠️ Note that we introduced a space in number name. This means we have lost the ability to use .dot notation with that column, so, in general: avoid using spaces in your column names.

Applying functions on columns

Applying a function on the entire column

Some functions can be applied on the entire column. For example, we can sum all the values in a column:

Pandas also has its own summary statistics methods, such as .median() and .std(), which we will study later.

Applying a function independently on each value of a column

As we saw above, we can apply some basic mathematical operations to each of the values:

... but what if we want to apply a more complicated function? We can use the .apply() method to apply any function we wish to every element in a column. This can be a pre-existing python function, or we can define our own function:

Fancier still, we can use Python's lambda functions to "define" a function on the fly; i.e., we don't have to create the function beforehand. For example, let's calculate the triple negative of the values:

A full understanding of the use of lambda above is beyond the scope of this tutorial, but I encourage you to look into it.

Getting the data types of columns

Easy:

Note: strings (i.e. text) are generically treated as an "object" in pandas, as is pretty much anything that isn't a number or Boolean (True/False).

Changing the data type of a column

One way to change the data type of a column is to reassign it to itself with a different type:

The type of triplenegs has changed from int64 to float32. And if we now print the dataframe again:

... we see that the triplenegs column has indeed changed from integer to float: it now has a decimal; it's zero in all cases, but it's there!

Boolean operations and filtering/subsetting the rows in a dataframe

Row filtering requires a bit of Boolean wizardry: you need to tell pandas which conditions you want your data to match. For example, to find out which of the multiples of three are also even, we ask pandas to show us which of those numbers give a residue (%) of 0 when divided by 2:

And as you can see, the result is a bool.

Filtering a dataframe with df[ filter ]

Now that we know how to make a Boolean filter, we can use it to ask pandas to show us only the elements that match that filter. In our example, to get the rows for which the threes column is even, we use the same filter as above and feed it to a [ ] pair for subsetting:

(the spaces inside the brackets are unnecessary, I just put them there for clarity)

This may seem a little confusing at first, particularly because of the double df outside and inside the brackets. What's happening here is the inner code creates the same list of Trues and Falses that we got above; we then send this list to df, so that pandas can decide what to show us from the dataframe: it shows us those rows for which it gets a True, skips those that have a False.

In fact, since we have six rows, we could use any combination of six True/False values:

Specifying multiple conditions with ( ) | ( ) and ( ) & ( )

Let's get all rows for which either the threes column is even, or the value in the number name column contains the text "een":

(remember that we can't use .dot notation with the number name column anymore because now it contains a space)

We used a list comprehension in the second condition above. Just in case you can't remember from the basic Python workshop how list comprehensions work, here is one to calculate the squares of the numbers from 0 to 9:

Using .loc[] and .iloc[] to filter and explore rows and columns

There are several ways to filter rows. Above we used simply:

df[ df.threes %2 == 0 ]

But this has limitations, and there are other ways worth exploring. One of the most popular and powerful is .loc[ ].

Using .loc to access elements in a dataframe

If we change the first df in the code above to df.loc, we get what seems to be the same result:

You may wonder why you would ever want to write df.loc when df alone suffices. The main advantage of df.loc[] over df[] is that df.loc[] gives you access to the dataframe itself, so you can make changes to it (as we will see soon), whereas with df[ ] notation you only get a copy of the data, so you can't make any permanent changes to it.

Filtering rows and columns by adding a comma

.loc can filter columns too. All we need to do is separate the row filter from the column filter using a comma. We must then specify which columns we want, which could simply be by name:

Or we could use separate Boolean filters for the row values and column names:

Here we used a list comprehension to tell pandas to get us any column that has the text "leneg" in its name. You can use any valid filter you can come up with.

Ultimately, what needs to happen here is we must give either specific names of the columns, or a list of Trues and Falses for both the rows and the columns:

⚠️ Note that the rows list must be 6 items long because we have six rows, and the columns list must be 5 items long because that's the number of columns in this dataframe at present. Any other lengths will throw an error.

Using .iloc (integer location) to access rows and column by position

The .iloc[] property gives us access to the rows of the dataframe by numbered position, starting at 0:

We can also give a range, which works as customarily in Python:

And we can also access the columns in the same way:

We can take another look at the raw dataframe to make sure that it worked well:

Using .loc (or .iloc) to replace values in a dataframe

Let's create a new column called is_even, and define it to False for starters:

Here's where .loc shows its powers. If we were to try to use df[ condition ][column] to try to change the value, the operation would not be permanent, because df[ ] only produces a copy of the data, it doesn't give access to the dataframe itself. In contrast, .loc both returns what we're looking for, and it gives us access to it, so we can use it to change values:

Note that .iloc functions in a similar way to .loc, i.e. you can use it to change values as well as just look at them.

Writing and reading data to/from text files

Writing to a .csv or .tsv file

This is trivially done. The default is comma-separated values (CSV), but if you want tab-separated (TSV) instead — or something else — you can just specify a separator with sep:

Reading in a .csv or .tsv file

Reading data in is just as easy as writing. We do it with:

df.read_csv('the_file.tsv', sep='\t')

You don't need to specify a separator if the data is in CSV.

Loading in a more realistic dataset

Since we're already familiar with many of the basics of pandas, let's load in a richer dataset (which you hopefully downloaded from my GitHub along with this notebook). This dataset contains a few details on phone and internet connectivity per country that I downloaded from the WorldBank. I did a bit of pre-cleaning in Excel and now this is what we have:

Note that this data is in "stacked" or "record" format, as is most of the data from the UN, WHO, and WorldBank. We need to transform it so that each country appears only once, and we have the indicator values for each year as columns.

Use pivot to transform a stacked dataframe to a multi-indexed one

This is very good. We now have a multi-indexed dataframe. We can use this multi-index to get only the values for 2015:

And if we want to get a column therein, all we have to do is filter twice:

And further down the rabbit hole, if we want the values for a specific country, we filter thrice (using the country code, which is our main index):

Flattening a multi-index dataframe

The multi-index dataframe that we just built is very cool, but it may get a little annoying for some purposes. For example, we can't immediately access the country name as we normally would by issuing:

wbdf['Country Name']

...it throws an error. Similarly, trying to access any of the indicator columns also throws an error (we have to specify the year first). So, we'll need to flatten the dataframe to make it more like a normal pandas dataframe.

Let's first take a look at the names of the columns:

It would seem the indices are tuples. We need to flatten those. But we do want to keep the year, so let's do that through a for loop that combines both the name of the indicator and the year:

Ok, we can do some fancy rearranging and replacement of spaces for underscores _ in the indicator name, and we get:

Good, now we can use this to feed a list of new column names. Actually, we can make it even fancier and use a list comprehension instead of the classic for loop:

Now we can use this list to replace the column names in the dataframe

This is looking good, but you'll notice that we still have a multi-level index. There are many ways to resolve this, but one I would like here is to add a new index to the dataframe, so that it has consecutive numbers as index like a default pandas dataframe. This will have the added advantage of allowing us to now use the Country Code and Country Name as regular pandas columns.

Resetting the index of a dataframe

Resetting the index of a dataframe adds a new 0..1..2..N index to the dataframe, and releases any oreset_index column(s) that may have been previously used as index, turning them into regular columns.

Nice. You will see that now our indices are flat and our dataframe looks just like any regular pandas dataframe.

Even better, we can now access the Country Code and Country Name as regular columns:

Or we can also get multiple columns:

Getting more than a sample of rows

You will have noticed that pandas is very friendly in not printing the entire rows to screen. But what if you do want them all? You can either print the desired column as a list with list(df.mycol), or, to stay within pandas, you can ask for the .values attribute:

⚠️ Extracting the .values is also helpful for columns with long text, which pandas cuts out by default.⚠️

Summary statistics

General high-level description of the data using .info and .describe()

We can get a very good summary of the data in the dataframe using the .describe() method:

Some further useful information can be found with .info(), most notably the number of entries (which is also above in "count"), and the data type (which we could instead get with wbdf.dtypes)

Summary statistics (mean, std, median, min, max, count)

The .describe() method provides a general view of several useful summary statistics, such as the mean, count, median (i.e., the 50% quantile), min, max, and standard deviation (std). All of these have their own methods too, which we can apply either on the entire dataframe or on specific columns.

If we run one of these summary methods on the entire dataframe, pandas calculates it for all possible columns, as shown below.

Summary statistics on the entire dataframe

(you will probably see a warning that you should drop the invalid columns before you compute the mean, because future versions of pandas won't filter them out by default. The problem is we have a couple of string (oject) columns with the country names and codes, and of course it's not possible to calculate their mean or std. For now, pandas will deal with it, but future versions will throw an error. As a homework, go ahead and change the code so that the text columns are filtered out and the warning disappears)

More typically, instead of calculating a summary statistic for the entire dataframe, we will want to do so on a specific column:

Extracting the positions of minimum and maximum values in a dataframe

While .min() and .max() tell us what the minimum and maximum values are, they don't tell us which rows (i.e. countries in this case) they belong to. The .idxmax() and .idxmin() methods give the index of the rows that contain the maximum and minimum values for a given column:

On their own, these indexes are not very informative, but we can use them to extract the names of the corresponding countries using .iloc:

Let's load another very interesting set of country indicator data.

(Note that I already "unstacked" (i.e. flattened) this one)

Two things worth noting right from the start:

We'll bump into both of these problems below as we attempt to merge the dataframes.

For now, let's look at a high-level summary/description of the contents of the dataframe:

This is very useful. For example, the count row tells us that only 36 countries have an entry for the two % Literacy columns, so these two may not be particularly useful and we may do better to just drop them:

Merging dataframes

We now want to build a Frankensteined dataframe with both sets of data (the one in wbdf with the mobile data and so on, and the one in wbdf2 with the more social stuff, plus CO2 emissions). Pandas provides two very handy functions for this purpose: join and merge. With merge, we can specify which column we want to use to merge the two dataframes. Unfortunately, wbdf2 doesn't have a Country Code column, so we will have to use the much more dangerous Country Name. This will likely be problematic, but let's try:

Looks good, the columns are now all together and they seem to be joined correctly. But take a look at the number of rows in the three dataframes (which we do simply by getting their lengths):

It appears we've lost 5 countries! Since we used the country names to merge, there must have been a problem there. Let's compare the two columns.

Comparing two columns

There are many ways to compare columns (e.g. you could use a for loop and if statements in regular python), but here's a fancy pandas way to find the countries in wbdf2 that are not (~) in the column Country Name of the first wbdf:

(I do not expect you to fully understand what that code is doing. For now, just do what most people do: copy and paste it from StackOverflow and then try to understand it if you want to and have the time, otherwise just make sure that it does what you need and move on. Briefly, it would read something like: "get the column Country from wbdf2, and filter it by those elements in the same column that are not in the Country Name column in wbdf.")

Let's do it the other way around, to find the countries that are in the first but not the second:

These are the same, but the differences are mostly extremely subtle in terms of spelling. There's also Eswatini, which is still referred to in the second dataframe by its now disfavoured name of Swaziland.

The trickiest problem is perhaps with the DPRK (i.e., North Korea), which has a ’ in one of the dataframes and a ' in the other... spot the difference? Many thousands of programmer hours are wasted every day because of these and their other quadruplet sisters, the backquote/backtick ` and the accent ´.

Replacing specific values in a dataframe with .replace()

Above we used a Boolean filter to change all the values in the dataframe that matched the filter. However, here I cannot think of any clever way of solving the problem of the five differing names programmatically. Instead, we will simply have to change each of them one by one. Pandas provides the method .replace() for this.

It doesn't matter which of the two dataframes we do it in, or we could do it in both, as long as we end up with the same name in both.

Let's change the five differing values in the second dataframe only. We could do it one by one (.replace(oldname, newname)), but we can also use a dictionary:

We use inplace=True so that we make the change into the dataframe itself, as opposed to returning a changed version.

Let's look for differences again:

Now the result is empty (there are no items that are in df2 but not in df1), so we can go ahead and create the merged dataframe again:

Very nice, we now have the correct 217 countries (rows).

Computing aggregate statistics per group with .groupby()

Now that we have a masterfully curated dataset, we can do some actualy data science on it. For example, let's compare the CO2 emissions (metric tons per capita) by income group of the country:

Gosh... if there's a hell, those of us living in high-income nations are probably headed straight there.

Actually, we don't really have to specify a column at the end, we can just get the mean for all values:

Besides mean(), other functions that we may want to use are count(), sum(), min(), max(), std() and median(), and in fact there are ways to use any function we wish.

Sorting columns

Let's take those latter result and sort them by the most sinful carbon producers:

...well, no surprises there.

How about that 2016 problem?

Our second imported data has only the values for 2016, whereas the first one had 2015 to 2019. If we want to analyse them together, should we keep the 2019 data from the first one since it's more recent, or should we use the 2016 data only so that it matches the status at the time of the other data? Both approaches may be defensible, and in your data research you'll need to make decisions like this. Just make sure to explain it clearly to yourself and to your colleagues/reviewers/peers/readers, especially by thoroughly annotating and publishing your Jupyter notebooks.

Plotting pandas data

Plotting in Matplotlib is covered in further detail in a separate notebook, but Pandas makes plotting its data extremely straightforward.

Let's first create a simple dataframe:

Plotting it is extremely easy:

Changing the default plotting software to Plotly

By default, pandas uses Matplotlib as its default plotting software. This can be changed to the more interactive Plotly:

Now we can use Plotly just like we used Matplotlib above:

You will see that Plotly graphs are dynamic! Scroll over the values to see further details in pop-up clouds. You can also select a box to zoom into, and double-click to get back to full view. Click on "cubes" in the legend to hide/show the cubes data...

This barely scratches the surface. Plotly is extremely powerful, and I strongly recommend it if you are considering advanced data visualisation. Here's a final example using the data from the merged World Bank data above:

Sending pandas dataframes to R and vice versa

You could of course export to TSV or CSV and then import into R in R-Studio, or vice versa, and sometimes that's the best solution. But you can in fact send dataframes between the two languages right here within Jupyter. We will cover that in the Interfacing R with Python lesson, but here's a quick demonstration of how easy this is.

First, we load the R extension for Jupyter:

And now we can declare a full cell as R code by using the %%R Jupyter "magic". We import the dataframe into R by using the -i flag, and from then on we can just do our normal everyday R, including plotting (in base R or ggplot) and such:

⚠️ This was only a brief demonstration; please see the Interfacing R with Python notebook for a more thorough introduction.